﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ContactManager.Common;

namespace ContactManager
{
    public partial class IncYearFrm : AlSkin.AlForm.AlBaseForm
    {
        public IncYearFrm()
        {
            InitializeComponent();
        }

        int year = 0;

        private void IncYearFrm_Load(object sender, EventArgs e)
        {
            this.labelContactName.Text = Constant.TITLE + GlobleUser.USER_NAME;
            
            //初始化最近联系人
            this.combRecent.Items.Clear();
            string recentSql = "select top 18 c.c_id,c.c_name,max(t.t_time) from contacters c,traces t where c.c_id = t.t_c_id group by c.c_id,c.c_name order by max(t.t_time) desc";
            DataSet recentDs = AccessHelper.ExecuteQuery(recentSql);
            foreach (DataRow dr in recentDs.Tables[0].Rows)
            {
                ComboBoxItem ci = new ComboBoxItem();
                ci.Text = Convert.ToString(dr["c_name"]);
                ci.Value = dr["c_id"];
                this.combRecent.Items.Add(ci);
            }

            //初始化时间
            DateTime now = DateTime.Now;
            year = now.Year;
            this.labYear.Text = "年度：" + year.ToString();
            //载入数据
            string sql = "and format(t.i_date,'yyyy') = " + year;
            initDataGridView(sql);
            getSum(sql);
        }

        private void combRecent_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBoxItem recent = (ComboBoxItem)this.combRecent.SelectedItem;
            TracesForm tf = new TracesForm(recent.Value.ToString());
            tf.ShowDialog();
        }

        private void btnNextYear_Click(object sender, EventArgs e)
        {
            if (year < DateTime.Now.Year)
            {
                year = year + 1;
                loadData();
            }
            else
            {
                year = DateTime.Now.Year;
            }
        }

        private void btnPreviousYear_Click(object sender, EventArgs e)
        {
            year = year - 1;
            loadData();
        }

        private void loadData()
        {
            this.labYear.Text = "年度：" + year;
            string sql = "and format(t.i_date,'yyyy') = " + year;
            initDataGridView(sql);
            getSum(sql);
        }

        private void initDataGridView(string sql)
        {
            this.dataGridViewIncYear.Rows.Clear();
            //传入and条件。主要用于日期条件
            string totalSql = "select format(t.i_date,'yyyy') as i_date,p.t_name as name,sum(IIF(t.i_iorp=0,t.i_num_one,0)) as inc_one,sum(IIF(t.i_iorp=0,t.i_num_second,0)) as inc_second," +
                              "sum(IIF(t.i_iorp=1,t.i_num_one,0)) as pay_one,sum(IIF(t.i_iorp=1,t.i_num_one,0)) as pay_second " +
                              "from incpay t,typeinc p where t.i_type_id = p.t_id " + sql + " group by format(t.i_date,'yyyy'),p.t_name";
            DataSet totalDs = AccessHelper.ExecuteQuery(totalSql);
            foreach (DataRow dr in totalDs.Tables[0].Rows)
            {
                string mdate = dr["i_date"].ToString();
                string tname = dr["name"].ToString();
                string inc_one = dr["inc_one"].ToString();
                string inc_second = dr["inc_second"].ToString();
                string pay_one = dr["pay_one"].ToString();
                string pay_second = dr["pay_second"].ToString();

                this.dataGridViewIncYear.Rows.Add(mdate, tname, inc_one, inc_second, pay_one, pay_second);
            }
        }

        private void dataGridViewIncYear_DoubleClick(object sender, EventArgs e)
        {
            if (this.dataGridViewIncYear.CurrentRow != null)
            {
                string year = this.dataGridViewIncYear.CurrentRow.Cells["m_date"].Value.ToString();
                IncMonthFrm imf = new IncMonthFrm(year);
                imf.ShowDialog();
            }
        }

        private void getSum(string sql)
        {
            string sumIncSql = "select iif(sum(t.i_num_one) is null,0,sum(t.i_num_one)) as sumone,iif(sum(t.i_num_one) is null,0,sum(t.i_num_second)) as sumsecond from incpay t where t.i_iorp = 0 " + sql;
            string sumPaySql = "select iif(sum(t.i_num_one) is null,0,sum(t.i_num_one)) as sumone,iif(sum(t.i_num_one) is null,0,sum(t.i_num_second)) as sumsecond from incpay t where t.i_iorp = 1 " + sql;
            int incSumOne = 0;
            int incSumSecond = 0;
            int paySumOne = 0;
            int paySumSecond = 0;
            DataSet incDs = AccessHelper.ExecuteQuery(sumIncSql);
            foreach (DataRow dr in incDs.Tables[0].Rows)
            {
                incSumOne = Convert.ToInt32(dr[0].ToString());
                incSumSecond = Convert.ToInt32(dr[1].ToString());
            }

            DataSet payDs = AccessHelper.ExecuteQuery(sumPaySql);
            foreach (DataRow dr in payDs.Tables[0].Rows)
            {
                paySumOne = Convert.ToInt32(dr[0].ToString());
                paySumSecond = Convert.ToInt32(dr[1].ToString());
            }

            this.labSumInc.Text = "本月统计  收入总和：" + incSumOne.ToString() + "  贷入总和：" + incSumSecond.ToString();
            this.labSumPay.Text = "支出总和：" + paySumOne.ToString() + "  借出总和：" + paySumSecond.ToString();
            this.labSumTotal.Text = "收支结余：" + (incSumOne - paySumOne).ToString();

        }

        private void btnExportExcel_Click(object sender, EventArgs e)
        {
            ExcelUtil.saveDataGridView(this.dataGridViewIncYear, "年统计");
        }

        private void IncYearFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            HomeFrm.iyf = null;
        }
    }
}
